import pymysql

def swap(line):
    """swap city and province column lest primary key error"""
    province = line.pop(0)
    city = line.pop(0)
    
    line.insert(0, city)
    line.insert(1, province)
    return line

db = pymysql.connect(host='localhost',
                     user='root',
                     password='thechosen2!',
                     database='campus_help')

cursor = db.cursor()

# read city and its location
filename = 'cityPosition.txt'
with open(filename) as f:
    data = f.readlines()

# insert data into database
cities = []
for line in data:
    line = line.replace('\t', ' ').replace('~', ' ').replace('\n', '')
    cities.append(line.split(' '))

sql = """insert into city
        (city, minLon, maxLon, minLat, maxLat)
        values (%s, %s, %s, %s, %s)"""

cursor.executemany(sql, cities)
db.commit()
print(cursor.rowcount)